﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using System.Collections.ObjectModel;

namespace RaiderManagerBackend
{
    class SQLiteCommunicator
    {
        private SQLiteConnection con;
        private SQLiteCommand cmd;
        private SQLiteDataAdapter DB;

        private void SetConnection()
        {
            con = new SQLiteConnection("Data Source=../../../Database/BCRaidTracker.db;Version=3;New=False;Compress=True;");
        }

        private string ExecuteQuery(string txtQuery)
        {
            string ret = string.Empty;
            try
            {
                SetConnection();
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = txtQuery;
                ret = cmd.ExecuteScalar().ToString();

                con.Close();
            }
            finally
            {
                con.Close();
            }
            return ret;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName">The name of the table for which we want to insert.</param>
        /// <param name="ColumnValuePairs">The values to insert into the table. These are set in pairs such as "RaidId=11111"</param>
        /// <returns>The rowid for this insert</returns>
        private int Insert(string tableName, params string[] ColumnValuePairs)
        {
            string columnString = "";
            string valueString = "";

            foreach (string pair in ColumnValuePairs)
            {
                string[] tString = pair.Split('=');
                columnString += tString[0] + ", ";
                valueString += "'" + tString[1] + "'" + ", ";
            }

            //remove any additional commas
            columnString = columnString.Remove(columnString.LastIndexOf(','));
            valueString = valueString.Remove(valueString.LastIndexOf(','));

            string tempSql = string.Format("insert into {0} ({1}) values ({2});", tableName, columnString, valueString);

            int ret = 0;

            try
            {
                SetConnection();
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = tempSql + "select last_insert_rowid() AS [ID]";
                ret = int.Parse(cmd.ExecuteScalar().ToString());

                con.Close();

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                con.Close();
                
            }
            return ret;
        }

        private void Update(string updateStatement)
        {
            try
            {
                SetConnection();
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = updateStatement;
                cmd.ExecuteNonQuery();

                con.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public int AddUpdatePlayer(Player p)
        {
            if (p.PlayerId == 0)
            {
                //new player.
                int PlayerId = Insert("Player", "Name=" + p.Name, "OverallGrade=" + p.OverallGrade, "PerformanceGrade=" + p.PerformanceGrade, "AtttendanceGrade=" + p.AttendanceGrade,
                    "OfficerGrade=" + p.OfficerGrade, "GuildRank=" + p.GuildRank);
                return PlayerId;
            }
            else
            {
                //update our current player
                Update(string.Format("Update player set Name = '{0}', OverallGrade = '{1}', PerformanceGrade = '{2}', AtttendanceGrade = '{3}', " +
                    "OfficerGrade = '{4}', GuildRank = '{5}' where PlayerId = {6};",
                    p.Name, p.OverallGrade, p.PerformanceGrade, p.AttendanceGrade, p.OfficerGrade, p.GuildRank, p.PlayerId));
                return p.PlayerId;
            }
        }

        public int AddUpdateRaid(Raid r)
        {
            if (r.RaidId == 0)
            {
                int RaidId = Insert("Raid", "Date=" + formatDate(r.Date));
                return RaidId;
            }
            else
            {
                Update(string.Format("Update Raid set Date = '{0}' where RaidId = {1}", formatDate(r.Date), r.RaidId));
                return r.RaidId;
            }
        }

        public int AddUpdateEncounter(Encounter e)
        {
            if (e.EncounterId == 0)
            {
                int encounterId = Insert("Encounter", "Boss=" + e.BossName, "Location=" + e.Location, "RaidId=" + e.Raid.RaidId.ToString());
                return encounterId;
            }
            else
            {
                Update(string.Format("Update Encounter set BossName = '{0}', RaidId = {1}, Location = {2} where EncounterId = {3}", 
                    e.BossName, e.Raid.RaidId, e.Location, e.EncounterId));
                return e.EncounterId;
            }
        }

        public int AddUpdateAttendanceEntry(AttendanceEntry ae)
        {
            if (ae.AttendanceEntryId == 0)
            {
                int entryId = Insert("Attendance", "AttendanceCode=" + (int)ae.AttendanceCode, "SignupCode=" + (int)ae.SignUpCode,
                    "PlayerId=" + ae.Player.PlayerId, "RaidId=" + ae.Raid.RaidId);
                return entryId;
            }
            else
            {
                Update(string.Format("Update Attendance set AttendanceCode = '{0}', SignupCode = '{1}', PlayerId = {2}, RaidId = {3} where AttendanceId = {4};",
                    ae.AttendanceCode, ae.SignUpCode, ae.Player.PlayerId, ae.Raid.RaidId, ae.AttendanceEntryId));
                return ae.AttendanceEntryId;
            }
        }

        public int AddUpdatePerformanceEntry(PlayerPerformanceEntry pe)
        {
            if (pe.PerformanceEntryId == 0)
            {
                int entryId = Insert("Performance", "RaidBotAve=" + pe.MedianDPSForSpecAndEncounter, "EncounterId=" + pe.Encounter.EncounterId,
                    "Variance=" + pe.DifferenceAsAPercentageOfMedian, "DPS=" + pe.DPS, "PlayerId=" + pe.Player.PlayerId, "RaidId=" + pe.Raid.RaidId);
                return entryId;
            }
            else
            {
                Update(string.Format("Update Performance set RaidBotAve = {0}, Variance = {1}, DPS = {2}, PlayerId = {3}, EncounterId = {4}, RaidId = {5}",
                    pe.MedianDPSForSpecAndEncounter, pe.DifferenceAsAPercentageOfMedian, pe.DPS, pe.Player.PlayerId, pe.Encounter.EncounterId, pe.Raid.RaidId));
                return pe.PerformanceEntryId;
            }
        }

        public ObservableCollection<Player> BuildPlayers()
        {
            DataSet DS = new DataSet();
            DataTable PlayersDataTable = new DataTable();
            ObservableCollection<Player> playerList = new ObservableCollection<Player>();

            SetConnection();
            try
            {
                con.Open();
                cmd = con.CreateCommand();
                string CommandText = "select * from Player;";
                DB = new SQLiteDataAdapter(CommandText, con);
                DB.Fill(DS);
                PlayersDataTable = DS.Tables[0];
                foreach (DataRow row in PlayersDataTable.Rows)
                {
                    Player p = new Player();
                    p.PlayerId = int.Parse(row["PlayerId"].ToString());
                    p.Name = (string)row["Name"];
                    p.OverallGrade = row["OverallGrade"].ToString();
                    p.PerformanceGrade = row["PerformanceGrade"].ToString();
                    p.AttendanceGrade = row["AtttendanceGrade"].ToString();
                    p.OfficerGrade = row["OfficerGrade"].ToString();
                    p.GuildRank = row["GuildRank"].ToString();

                    playerList.Add(p);
                }

                con.Close();
            }
            finally
            {
                con.Close();
            }

            return playerList;
        }

        public ObservableCollection<Raid> BuildRaids()
        {
            DataSet raids = new DataSet();
            DataTable RaidsDataTable = new DataTable();
            ObservableCollection<Raid> RaidsList = new ObservableCollection<Raid>();

            SetConnection();
            try
            {
                con.Open();
                cmd = con.CreateCommand();
                string raidQuery = "select * from Raid";
                DB = new SQLiteDataAdapter(raidQuery, con);
                DB.Fill(raids);
                RaidsDataTable = raids.Tables[0];
                foreach (DataRow row in raids.Tables[0].Rows)
                {
                    Raid r = GetRaid(int.Parse(row["RaidId"].ToString()));
                    RaidsList.Add(r);
                }

                con.Close();
            }
            finally
            {
                con.Close();
            }
            return RaidsList;
        }

        public List<AttendanceEntry> BuildAttendanceForRaid(Raid raid)
        {
            DataSet attendance = new DataSet();
            List<AttendanceEntry> attendanceList = new List<AttendanceEntry>();

            SetConnection();
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                } 
                cmd = con.CreateCommand();
                string attendanceQuery = string.Format("select * from Attendance as a " +
                        "where a.RaidId = {0}", raid.RaidId);
                DB = new SQLiteDataAdapter(attendanceQuery, con);
                DB.Fill(attendance);
                DataTable attendanceDT = attendance.Tables[0];
                foreach (DataRow row in attendanceDT.Rows)
                {
                    Player p = GetPlayer(int.Parse(row["PlayerId"].ToString()));
                    AttendanceEntry ae = new AttendanceEntry(p, raid);
                    ae.AttendanceCode = (Enumerations.AttendanceCode)row["AttendanceCode"];
                    ae.SignUpCode = (Enumerations.SignUpCode)row["SignUpCode"];
                    ae.AttendanceEntryId = int.Parse(row["AttendanceId"].ToString());
                    attendanceList.Add(ae);
                }

                con.Close();
                return attendanceList;
            }
            finally
            {
                con.Close();
            }
        }

        public List<Encounter> BuildEncountersForRaid(Raid r)
        {
            DataSet encounterDS = new DataSet();
            List<Encounter> encounterList = new List<Encounter>();

            SetConnection();
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                cmd = con.CreateCommand();
                string encounterQuery = string.Format("select * from Encounter " +
                    "where RaidId = {0};", r.RaidId);
                DB = new SQLiteDataAdapter(encounterQuery, con);
                DB.Fill(encounterDS);
                DataTable attendanceDT = encounterDS.Tables[0];
                foreach (DataRow row in attendanceDT.Rows)
                {
                    Encounter e = new Encounter(r);
                    e.BossName = row["Boss"].ToString();
                    e.EncounterId = int.Parse(row["EncounterId"].ToString());
                    e.Location = row["Location"].ToString();
                    e.Raid = GetRaid(int.Parse(row["RaidId"].ToString()));
                    e.PerformanceEntries = BuildPerformanceForEncounter(e);
                    encounterList.Add(e);
                }

                con.Close();
                return encounterList;
            }
            finally
            {
                con.Close();
            }
        }


        /// <summary>
        /// Build attendance will generate the attendance for the player.
        /// </summary>
        /// <param name="p">The player we want to get all of the attendance for.</param>
        /// <returns>The attendance to be assigned to the player's attendanceentries</returns>
        public List<AttendanceEntry> BuildAttendanceForPlayer(Player p)
        {
            DataSet attendance = new DataSet();
            List<AttendanceEntry> attendanceList = new List<AttendanceEntry>();

            SetConnection();
            try
            {
                con.Open();
                cmd = con.CreateCommand();
                string attendanceQuery = string.Format("select * from Attendance as a " +
                    "where a.PlayerId = {0};", p.PlayerId);
                DB = new SQLiteDataAdapter(attendanceQuery, con);
                DB.Fill(attendance);
                DataTable attendanceDT = attendance.Tables[0];
                foreach (DataRow row in attendanceDT.Rows)
                {
                    Raid r = GetRaid(int.Parse(row["RaidID"].ToString()));

                    AttendanceEntry ae = new AttendanceEntry(p, r);
                    ae.AttendanceEntryId = int.Parse(row["AttendanceId"].ToString());
                    ae.AttendanceCode = (Enumerations.AttendanceCode)row["AttendanceCode"];
                    ae.SignUpCode = (Enumerations.SignUpCode)row["SignUpCode"];
                    attendanceList.Add(ae);
                }

                con.Close();
                return attendanceList;
            }
            finally
            {
                con.Close();
            }
        }

        public List<PlayerPerformanceEntry> BuildPerformanceForPlayer(Player p)
        {
            DataSet performance = new DataSet();
            List<PlayerPerformanceEntry> performanceList = new List<PlayerPerformanceEntry>();

            SetConnection();
            try
            {
                con.Open();
                cmd = con.CreateCommand();
                //get the 
                string performanceQuery = string.Format("select * from Performance where playerId = {0}", p.PlayerId);

                DB = new SQLiteDataAdapter(performanceQuery, con);
                DB.Fill(performance);
                DataTable performanceDT = performance.Tables[0];
                foreach (DataRow row in performanceDT.Rows)
                {
                    //build the information we know.

                    Encounter e = GetEncounter(int.Parse(row["EncounterId"].ToString()));
                    Raid r = GetRaid(int.Parse(row["RaidId"].ToString()));
                    PlayerPerformanceEntry pe = new PlayerPerformanceEntry(r, e, p);
                    pe.DifferenceAsAPercentageOfMedian = double.Parse(row["Variance"].ToString());
                    pe.DPS = int.Parse(row["DPS"].ToString());
                    pe.MedianDPSForSpecAndEncounter = int.Parse(row["RaidBotAve"].ToString());
                    pe.PerformanceEntryId = int.Parse(row["PerformanceId"].ToString());

                    performanceList.Add(pe);
                }

                con.Close();

                return performanceList;
            }
            finally
            {
                con.Close();
            }
        }

        
        private List<PlayerPerformanceEntry> BuildPerformanceForEncounter(Encounter e)
        {
            DataSet performance = new DataSet();
            List<PlayerPerformanceEntry> performanceList = new List<PlayerPerformanceEntry>();

            SetConnection();
            try
            {
                con.Open();
                cmd = con.CreateCommand();
                //get the 
                string performanceQuery = string.Format("select * from Performance where EncounterId = {0}", e.EncounterId);

                DB = new SQLiteDataAdapter(performanceQuery, con);
                DB.Fill(performance);
                DataTable performanceDT = performance.Tables[0];
                foreach (DataRow row in performanceDT.Rows)
                {
                    //build the information we know.

                    Player p = GetPlayer(int.Parse(row["PlayerId"].ToString()));
                    Raid r = GetRaid(int.Parse(row["RaidId"].ToString()));
                    PlayerPerformanceEntry pe = new PlayerPerformanceEntry(r, e, p);
                    pe.DifferenceAsAPercentageOfMedian = double.Parse(row["Variance"].ToString());
                    pe.DPS = int.Parse(row["DPS"].ToString());
                    pe.MedianDPSForSpecAndEncounter = int.Parse(row["RaidBotAve"].ToString());
                    pe.PerformanceEntryId = int.Parse(row["PerformanceId"].ToString());

                    performanceList.Add(pe);
                }

                con.Close();

                return performanceList;
            }
            finally
            {
                con.Close();
            }
        }

        public Encounter GetEncounter(int encounterId)
        {
            DataSet encounterDS = new DataSet();

            SetConnection();
            try
            {
                con.Open();
                cmd = con.CreateCommand();

                string encounterQuery = string.Format("select * from Encounter where EncounterId = {0}", encounterId);

                DB = new SQLiteDataAdapter(encounterQuery, con);
                DB.Fill(encounterDS);

                DataTable encounterDT = encounterDS.Tables[0];
                DataRow row = encounterDT.Rows[0];
                Raid r = GetRaid(int.Parse(row["RaidId"].ToString()));
                Encounter encounter = new Encounter(r);
                encounter.BossName = (string)row["Boss"];
                encounter.EncounterId = int.Parse(row["EncounterId"].ToString());
                encounter.Location = (string)row["Location"];

                con.Close();

                return encounter;
            }
            finally
            {
                con.Close();
            }
            
        }

        /// <summary>
        /// returns a sparse player that only includes the grades.
        /// </summary>
        /// <param name="playerId"></param>
        /// <returns></returns>
        public Player GetPlayer(int playerId)
        {
            DataSet playerDataSet = new DataSet();

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                cmd = con.CreateCommand();
                string attendanceQuery = string.Format("select * from Player " +
                    "where PlayerId = {0};", playerId);
                DB = new SQLiteDataAdapter(attendanceQuery, con);
                DB.Fill(playerDataSet);
                DataTable playerDT = playerDataSet.Tables[0];
                Player p = new Player();
                p.PlayerId = playerId;
                DataRow playerRow = playerDT.Rows[0];
                p.Name = (string)playerRow["Name"];
                p.AttendanceGrade = (string)playerRow["AtttendanceGrade"];
                p.OverallGrade = (string)playerRow["OverallGrade"];
                p.PerformanceGrade = (string)playerRow["PerformanceGrade"];
                p.OfficerGrade = (string)playerRow["OfficerGrade"];
                p.GuildRank = (string)playerRow["GuildRank"];

                return p;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// Gets a sparse raid. In this case, only fills the date.
        /// </summary>
        /// <param name="raidId"></param>
        /// <returns></returns>
        public Raid GetRaid(int raidId)
        {
            DataSet raidDS = new DataSet();

            Raid r = new Raid();
            r.RaidId = raidId;
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                cmd = con.CreateCommand();
                string raidQuery = string.Format("select Date from Raid " +
                    "where RaidId = {0};", raidId);
                DB = new SQLiteDataAdapter(raidQuery, con);
                DB.Fill(raidDS);
                DataTable raidTable = raidDS.Tables[0];
                
                r.Date = (DateTime)raidTable.Rows[0]["Date"];

                return r;
            }
            finally
            {
                con.Close();
            }
        }

        private string formatDate(DateTime date)
        {
            return date.ToString("yyyy-MM-dd HH:mm:ss.fffffff");
        }
    }
}
